#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


insert into hive.edu_dm.dm_relationship
with temp as (
    select '2023-5-8'                        as date_time,
           ------时间粒度标记------
           case
               when grouping(hour_date) = 0
                   then 'hour'
               when grouping(day_date) = 0
                   then 'day'
               when grouping(month_date) = 0
                   then 'month'
               when grouping(year_date) = 0
                   then 'year'
               else null end                 as dt_group_type,

           case
               when grouping(tdepart_id, dep_name) = 0
                   then 'tdepart'
               when grouping(origin_channel) = 0
                   then 'origin_channel'
               when grouping(itcast_school_id, school_name) = 0
                   then 'itcast_school'
               when grouping(itcast_subject_id, subject_name) = 0
                   then 'itcast_subject'
               when grouping(area) = 0
                   then 'area'
               when grouping(origin_type) = 0
                   then 'origin_type'
               when grouping(clue_state) = 0
                   then 'clue_state'
               when grouping(day_date) = 0
                   then 'all'

               else 'others'
               end                           as group_type_new,

           ------维度------
           origin_type,                                                      -- 线上线下
           itcast_school_id,                                                 --校区
           school_name,
           itcast_subject_id,                                                --学科
           subject_name,
           origin_channel,                                                   --来源渠道
           area,                                                             -- 地区
           tdepart_id,
           dep_name,                                                         --部门
           clue_state,                                                       -- 新老学员
           year_date,
           month_date,
           day_date,
           hour_date,
           group_type                        as group_type_old,
           ------指标-------
           sum(customer_relationship_amount) as customer_relationship_amount --意向用户个数

    from hive.edu_dta.relationship_day
    group by
        grouping sets (
        --------------------年上卷-------------------------
        (year_date, group_type, origin_type, clue_state),
        (year_date, group_type, origin_type, clue_state, area),
        (year_date, group_type, origin_type, clue_state, itcast_subject_id, subject_name),
        (year_date, group_type, origin_type, clue_state, itcast_school_id, school_name),
        (year_date, group_type, origin_type, clue_state, origin_channel),
        (year_date, group_type, origin_type, clue_state, tdepart_id, dep_name),
        ----------------------月上卷---------------------------
        (year_date, month_date, group_type, origin_type, clue_state),
        (year_date, month_date, group_type, origin_type, clue_state, area),
        (year_date, month_date, group_type, origin_type, clue_state, itcast_subject_id, subject_name),
        (year_date, month_date, group_type, origin_type, clue_state, itcast_school_id, school_name),
        (year_date, month_date, group_type, origin_type, clue_state, origin_channel),
        (year_date, month_date, group_type, origin_type, clue_state, tdepart_id, dep_name),
        -----------------------------日下钻-------------------------------
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state),
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state, area),
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state, itcast_subject_id,
         subject_name),
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state, itcast_school_id,
         school_name),
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state, origin_channel),
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state, tdepart_id, dep_name)

        ))
select *
from temp
where group_type_new = group_type_old;



insert into hive.edu_dm.dm_clue
with temp as (
    select '2023-5-8'        as date_time,
           ------时间粒度标记------
           case
               when grouping(hour_date) = 0
                   then 'hour'
               when grouping(day_date) = 0
                   then 'day'
               when grouping(month_date) = 0
                   then 'month'
               when grouping(year_date) = 0
                   then 'year'

               else null end as dt_group_type,

           case

               when grouping(origin_type, clue_state) = 0
                   then 'origin_type,clue_state'
               when grouping(day_date) = 0
                   then 'all'
               else 'others'
               end           as group_type_new,

           ------维度------
           origin_type,                     -- 线上线下
           clue_state,                      -- 新老学员
           year_date,
           month_date,
           day_date,
           hour_date,
           group_type        as group_type_old,
           ------指标-------
           sum(clue_amount)  as clue_amount ,--总线索个数
            sum (val_clue_amount) as val_clue_amount
    from hive.edu_dta.clue_day
    group by
        grouping sets (
        -----------------------------日-------------------------------
        (year_date, month_date, day_date, group_type, origin_type, clue_state),
        ---------------------------------小时---------------------------------
        (year_date, month_date, day_date, hour_date, group_type, origin_type, clue_state)
        ))
select *
from temp
where group_type_new = group_type_old;



